package com.hylanda.pb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.hylanda.pb.entity.ReportFile;

/** 
 * @author zhangy
 * @E-mail:blackoon88@gmail.com 
 * @qq:846579287
 * @version created at：2018年2月6日 下午3:58:44 
 * note JpaRepository<ReportFile, Long>,JpaSpecificationExecutor<ReportFile>
 */
@Repository
public class ReportFileDao extends SqlServerBaseDao {
	public JdbcTemplate getTemplate(){
		return super.template;
	}
	/*
	 * 本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询
	 */
	//SELECT TOP ?1 id FROM(SELECT ROW_NUMBER () OVER (ORDER BY id ASC) RowNumber ,* FROM sys_report_files) A WHERE A.RowNumber > (?2 - 1) * ?1

//	public Page<ReportFile> findPage(ReportFileQo reportFileQo) {
//		Pageable pageable = new PageRequest(reportFileQo.getPage(), reportFileQo.getSize(),Sort.Direction.DESC, "id");
//		int minRowNumber=(reportFileQo.getPage())*reportFileQo.getSize();
//		String sql="SELECT TOP "+reportFileQo.getSize()+" id,filename,date FROM(SELECT ROW_NUMBER () OVER (ORDER BY id DESC) RowNumber ,* FROM sys_report_files ) A WHERE A.RowNumber >"+minRowNumber;
//		if(!StringUtils.isEmpty(reportFileQo.getFilename())){
//			sql="SELECT TOP "+reportFileQo.getSize()+" id,filename,date FROM(SELECT ROW_NUMBER () OVER (ORDER BY id DESC) RowNumber ,* FROM sys_report_files where filename like'%"+reportFileQo.getFilename()+"%') A WHERE A.RowNumber >"+minRowNumber;
//		}
//		List<Map<String,Object>> list=super.template.queryForList(sql);
//		List<ReportFile> ll=new ArrayList<>();
//		for(Map<String,Object> map:list){
//			ReportFile rf=new ReportFile();
//			rf.setId((Long)map.get("id"));
//			rf.setFilename((String)map.get("filename"));
//			rf.setDate((Timestamp)map.get("date"));
//			ll.add(rf);
//		}
//		int total =queryCount("select count(*) from sys_report_files");
//		return new PageImpl<ReportFile>(ll, pageable, total);
//	}

	public int delete(Long id) {
		String sql="delete from sys_report_files where id=?";
		return super.template.update(sql, new Object[]{id});
	}

	public ReportFile save(final ReportFile entity) {
		 KeyHolder keyHolder = new GeneratedKeyHolder(); 
		 super.template.update(new PreparedStatementCreator(){  
             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException{  
                 int i = 0;  
                 String strSql="insert into sys_report_files (filename,filecontent,date) values (?,?,?)";
                 PreparedStatement ps = conn.prepareStatement(strSql, Statement.RETURN_GENERATED_KEYS);  
                 ps.setString(++i,entity.getFilename());
                 ps.setBytes(++i,entity.getFilecontent());
                 Timestamp time=new Timestamp(entity.getDate().getTime());
                 ps.setTimestamp(++i,time);
                 return ps;  
             }  
         },keyHolder);
		 long pid= keyHolder.getKey().longValue();
		 entity.setId(pid);
		 return entity;
	}

	public String findOne(Long id) {
		String sql="select filename from sys_report_files where id=?";
		return super.template.queryForObject(sql,String.class, new Object[]{id});
	}

	public String findByName(String name) {
		String sql="select filename from sys_report_files where filename=?";
		List<String> list=super.template.queryForList(sql, new Object[]{name}, String.class);
		if(list==null||list.size()==0){
			return null;
		}
		return list.get(0);
	}

}
